package com.iweb.dao.impl;

import com.iweb.dao.BaseDao;
import com.iweb.dao.UserDao;
import com.iweb.entity.User;
import com.iweb.util.DBHelper;
import com.iweb.util.Pager;
import com.iweb.util.StringHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;


public class UserDaoImpl extends BaseDao implements UserDao {

  @Override
  public List<User> selectPagedList(Integer offset, Integer rows) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      // 1.打开连接
      connection = DBHelper.getConnection();
      // 2.编写sql语句
      String sql = "select * from littlemall_user limit ?,?";
      preparedStatement = connection.prepareStatement(sql);

      // 执行
      resultSet = this.executeQuery(connection, preparedStatement, offset, rows);
      // 4.解析结果
      List<User> userList = new ArrayList<>();
      User user = null;
      while (resultSet.next()) {
        user = this.parseResultSet(resultSet);
        userList.add(user);
      }
      return userList;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }
  }

  @Override
  public int selectTotalCount() throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      // 1.打开连接
      connection = DBHelper.getConnection();
      // 2.编写sql语句
      String sql = "select count(1) from littlemall_user";
      // 3.执行
      preparedStatement = connection.prepareStatement(sql);
      // 执行
      resultSet = preparedStatement.executeQuery();
      // 4.解析结果
      int count = 0;
      if (resultSet.next()) {
        count = resultSet.getInt(1);
      }
      return count;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }
  }

  @Override
  public List<User> selectPagedList(Integer offset, Integer rows, String userName) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    // 保存sql的参数对应的值
    List<Object> params = new ArrayList<>();
    try {
      // 1.打开连接
      connection = DBHelper.getConnection();
      // 2.编写sql语句
      StringBuilder sb = new StringBuilder();
      sb.append("select * from littlemall_user ");
      // 判断查询参数,并添加参数值
      if (!StringHelper.isEmpty(userName)) {
        sb.append("where userName like ? ");
        params.add("%" + userName + "%");
      }
      sb.append("limit ?,? ");
      // 添加分页参数值
      params.add(offset);
      params.add(rows);

      preparedStatement = connection.prepareStatement(sb.toString());

      // 执行
      resultSet = this.executeQuery(connection, preparedStatement, params.toArray());
      // 4.解析结果
      List<User> userList = new ArrayList<>();
      User user = null;
      while (resultSet.next()) {
        user = this.parseResultSet(resultSet);
        userList.add(user);
      }
      return userList;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }
  }

  @Override
  public List<User> selectPagedList(Pager<User> pager) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    // 保存sql的参数对应的值
    try {
      // 1.打开连接
      connection = DBHelper.getConnection();
      // 2.编写sql语句
      List<Object> params = new ArrayList<>();
      StringBuilder sb = new StringBuilder();
      sb.append("select * from littlemall_user where 1=1 ");

      User user = pager.getParams();
      if (user != null) {
        // 用户名条件
        if (!StringHelper.isEmpty(user.getUserName())) {
          sb.append("and userName like ? ");
          params.add("%" + user.getUserName() + "%");
        }
        // 手机号条件
        if (!StringHelper.isEmpty(user.getMobile())) {
          sb.append("and mobile like ? ");
          params.add("%" + user.getMobile() + "%");
        }
        // 用户类型条件
        if (user.getType() != null) {
          sb.append("and type = ? ");
          params.add(user.getType());
        }
      }

      sb.append("limit ?,? ");
      // 添加分页参数值
      params.add(pager.getOffset());
      params.add(pager.getPageSize());

      preparedStatement = connection.prepareStatement(sb.toString());
      // 执行
      resultSet = this.executeQuery(connection, preparedStatement, params.toArray());
      // 4.解析结果
      List<User> userList = new ArrayList<>();
      User resultUser = null;
      while (resultSet.next()) {
        resultUser = this.parseResultSet(resultSet);
        userList.add(resultUser);
      }
      return userList;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }
  }

  @Override
  public int selectTotalCount(String userName) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      // 1.打开连接
      connection = DBHelper.getConnection();
      // 2.编写sql语句
      String sql = "select count(1) from littlemall_user";
      if (!StringHelper.isEmpty(userName)) {
        sql += " where userName like ?";
      }
      // 3.执行
      preparedStatement = connection.prepareStatement(sql);
      if (!StringHelper.isEmpty(userName)) {
        preparedStatement.setString(1, "%" + userName + "%");
      }
      // 执行
      resultSet = preparedStatement.executeQuery();
      // 4.解析结果
      int count = 0;
      if (resultSet.next()) {
        count = resultSet.getInt(1);
      }
      return count;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }
  }

  @Override
  public int selectTotalCount(User user) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      // 1.打开连接
      connection = DBHelper.getConnection();
      // 2.编写sql语句
      List<Object> params = new ArrayList<>();
      StringBuilder sb = new StringBuilder();
      sb.append("select count(1) from littlemall_user where 1=1 ");

      // 用户名条件
      if (!StringHelper.isEmpty(user.getUserName())) {
        sb.append("and userName like ? ");
        params.add("%" + user.getUserName() + "%");
      }
      // 手机号条件
      if (!StringHelper.isEmpty(user.getMobile())) {
        sb.append("and mobile like ? ");
        params.add("%" + user.getMobile() + "%");
      }
      // 用户类型条件
      if (user.getType() != null) {
        sb.append("and type = ? ");
        params.add(user.getType());
      }

      // 3.执行
      preparedStatement = connection.prepareStatement(sb.toString());
      // 执行
      resultSet = this.executeQuery(connection, preparedStatement, params.toArray());
      // 4.解析结果
      int count = 0;
      if (resultSet.next()) {
        count = resultSet.getInt(1);
      }
      return count;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }
  }

  @Override
  public List<User> selectList() throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      //1.打开连接
      connection = DBHelper.getConnection();
      //2.编写sql语句
      String sql = "select * from littlemall_user";
      //3.执行
      preparedStatement = connection.prepareStatement(sql);
      //执行
      resultSet = preparedStatement.executeQuery();
      //4.解析结果
      List<User> userList = new ArrayList<>();
      User user = null;
      while (resultSet.next()) {
        user = new User();
        user.setId(resultSet.getInt("id"));
        user.setLoginName(resultSet.getString("loginName"));
        user.setUserName(resultSet.getString("userName"));
        user.setPassword(resultSet.getString("password"));
        user.setSex(resultSet.getInt("sex"));
        user.setIdentityCode(resultSet.getString("identityCode"));
        user.setEmail(resultSet.getString("email"));
        user.setType(resultSet.getInt("type"));
        userList.add(user);
      }
      return userList;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }
  }

  @Override
  public User selectUserByLoginName(String loginName) throws Exception {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
      //1.打开连接
      connection = DBHelper.getConnection();
      //2.编写sql语句
      String sql = "select * from littlemall_user where loginName=?";
      //3.执行
      preparedStatement = connection.prepareStatement(sql);
      //准备参数
      preparedStatement.setString(1, loginName);
      //执行
      resultSet = preparedStatement.executeQuery();
      //4.解析结果
      User user = null;
      if (resultSet.next()) {
        user=parseResultSet(resultSet);
      }
      return user;
    } finally {
      DBHelper.close(resultSet, preparedStatement, connection);
    }

  }


  @Override
  public int delete(Integer id) throws Exception{
    String sql="delete from littlemall_user where id=?";
    int ret=this.executeUpdate(sql,id);
    return ret;
  }

  @Override
  public void insert(User user) throws Exception {
    String sql="insert into littlemall_user values(null,?,?,?,?,?,?,?,?)";
    //设置参数
    Object[] params={
      user.getLoginName(),
      user.getUserName(),
      user.getPassword(),
      user.getSex(),
      user.getIdentityCode(),
      user.getEmail(),
      user.getMobile(),
      user.getType()
    };
    this.executeUpdate(sql,params);
  }

  @Override
  public int update(User user) throws Exception {
    String sql="update littlemall_user set userName=?,sex=?,identityCode=?,email=?,mobile=?,type=? where id=?";
    //设置参数
    Object[] params={
            user.getUserName(),
            user.getSex(),
            user.getIdentityCode(),
            user.getEmail(),
            user.getMobile(),
            user.getType(),
            user.getId()
    };
    return this.executeUpdate(sql,params);
  }


  @Override
  public User selectUserById(Integer id) throws Exception {
    Connection connection=null;
    PreparedStatement preparedStatement=null;
    ResultSet resultSet=null;
    try{
      connection=DBHelper.getConnection();
      String sql="select * from littlemall_user where id=?";
      preparedStatement=connection.prepareStatement(sql);
      resultSet=this.executeQuery(connection,preparedStatement,id);
      //解析结果
      User user=null;
      if(resultSet.next()){
        user=parseResultSet(resultSet);
      }
      return user;
    }finally {
      DBHelper.close(resultSet,preparedStatement,connection);
    }
  }

  /**
   * 解析ResultSet结果
   * @param resultSet 已经被指向有结果行的resultSet
   * @return
   * @throws SQLException
   */
  private User parseResultSet(ResultSet resultSet) throws SQLException {
    User user = new User();
    user.setId(resultSet.getInt("id"));
    user.setLoginName(resultSet.getString("loginName"));
    user.setUserName(resultSet.getString("userName"));
    user.setPassword(resultSet.getString("password"));
    user.setSex(resultSet.getInt("sex"));
    user.setIdentityCode(resultSet.getString("identityCode"));
    user.setEmail(resultSet.getString("email"));
    user.setType(resultSet.getInt("type"));
    return user;
  }
}